/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.nxlv2.nomad.accessdb.dao.jdbc;

import com.nxlv2.nomad.accessdb.dao.DAOException;
import com.nxlv2.nomad.accessdb.dao.IArticleDao;
import com.nxlv2.nomad.accessdb.dao.IncoherentResultException;
import com.nxlv2.nomad.accessdb.dao.NoResultException;
import com.nxlv2.nomad.businessobjects.Article;
import com.nxlv2.nomad.businessobjects.DetailCommande;
import com.nxlv2.nomad.businessobjects.DetailDemande;
import com.nxlv2.nomad.businessobjects.Prix;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * @author Xavier
 */
public class JdbcArticleDao implements IArticleDao {

    private static final Logger logger = LoggerFactory.getLogger(JdbcArticleDao.class);
    private static final ConnectionManager manager = new ConnectionManager();
    /**
     * 
     * @param article
     * @throws DAOException 
     */
    @Override
    public void saveArticle(Article article) throws DAOException {
        if (null == article) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        Connection con = manager.creerConnection();

        StringBuilder builder = new StringBuilder();
        builder.append("INSERT INTO article (idBase, libelle, reference, dateCreation) ");
        builder.append("VALUES (?, AES_ENCRYPT(?,?), AES_ENCRYPT(?,?), NOW())");
        String request = builder.toString();
        logger.debug("Insert article: {}", request);

        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request, PreparedStatement.RETURN_GENERATED_KEYS);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, article.getIdBase());
            ps.setString(2, article.getLibelle());
            ps.setString(3, manager.getKeyAES());
            ps.setString(4, article.getReference());
            ps.setString(5, manager.getKeyAES());
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not populate request with data.");
            throw new DAOException("Can not populate request with data.", e);
        }

        try {
            ResultSet rs = ps.getGeneratedKeys();

            int pk = 0;

            while (rs.next()) {
                pk = rs.getInt(1);
            }
            article.setIdAppli(pk);

        } catch (SQLException e) {
            logger.error("Can not execute insert in db.");
            throw new DAOException("Can not execute insert in db.", e);
        }
        manager.fermerConnection();
    }
    /**
     * 
     * @param id
     * @throws DAOException 
     */
    @Override
    public void deleteArticle(int id) throws DAOException {

        if (id < 1) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        Connection con = manager.creerConnection();

        StringBuilder builder = new StringBuilder();
        builder.append("DELETE FROM article WHERE idAppli = ?");
        String request = builder.toString();
        logger.debug("Delete article: {}", request);

        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, id);
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not delete article with id.");
            throw new DAOException("Can not delete article with id.", e);
        }
        manager.fermerConnection();
    }
    /**
     * 
     * @param article
     * @throws DAOException 
     */
    @Override
    public void updateArticle(Article article) throws DAOException {
        if (null == article) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        Connection con = manager.creerConnection();

        StringBuilder builder = new StringBuilder();

        builder.append("UPDATE article SET ");
        builder.append("libelle = AES_ENCRYPT(?,?),");
        builder.append("reference = AES_ENCRYPT(?,?),");
        builder.append("dateModification = NOW(), ");
        builder.append("idBase = ? ");
        builder.append("WHERE idAppli = ?");

        String request = builder.toString();
        logger.debug("Update article: {}", request);

        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setString(1, article.getLibelle());
            ps.setString(2, manager.getKeyAES());
            ps.setString(3, article.getReference());
            ps.setString(4, manager.getKeyAES());
            ps.setInt(5, article.getIdBase());
            ps.setInt(6, article.getIdAppli());
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not populate request with data.");
            throw new DAOException("Can not populate request with data.", e);
        }
        manager.fermerConnection();
    }
    /**
     *
     * @param id
     * @return Article
     * @throws DAOException
     */
    @Override
    public Article findArticleById(int id) throws DAOException {
        if (id < 1) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT idBase, idAppli, ");
        builder.append("AES_DECRYPT(libelle, ?) AS libelle,");
        builder.append("AES_DECRYPT(reference,?) AS reference ");
        builder.append("FROM article WHERE idAppli = ?");

        String request = builder.toString();
        logger.debug("get article: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            for (int i = 1; i < 3; i++) {
                ps.setString(i, manager.getKeyAES());
            }
            ps.setInt(3, id);
            ps.executeQuery();

        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }
        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<Article> articles = null;
        try {
            articles = map(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.");
            throw new DAOException("Can not populate map with result.", e);
        }

        if (null == articles || articles.isEmpty()) {
            throw new NoResultException("No adresse found with id " + id);
        }
        if (articles.size() > 1) {
            throw new IncoherentResultException("Many records with a same id.");
        }
        manager.fermerConnection();
        return articles.get(0);
    }
    @Override
    public Article existByIdbase(int id) throws DAOException {
        if (id < 1) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT idBase, idAppli, ");
        builder.append("AES_DECRYPT(libelle, ?) AS libelle,");
        builder.append("AES_DECRYPT(reference,?) AS reference ");
        builder.append("FROM article WHERE idBase = ?");

        String request = builder.toString();
        logger.debug("get article: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            for (int i = 1; i < 3; i++) {
                ps.setString(i, manager.getKeyAES());
            }
            ps.setInt(3, id);
            ps.executeQuery();

        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }
        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<Article> articles = null;
        try {
            articles = map(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.");
            throw new DAOException("Can not populate map with result.", e);
        }

        if (null == articles || articles.isEmpty()) {
            return new Article();
        }
        if (articles.size() > 1) {
            throw new IncoherentResultException("Many records with a same id.");
        }
        manager.fermerConnection();
        return articles.get(0);
        
//        if (1 == articles.size()){
//            return true;
//        }
//        if (null == articles || articles.isEmpty()) {
//           return false;
//        }
//        if (articles.size() > 1) {
//            throw new IncoherentResultException("Many records with a same idbase.");
//        }
//        return false;
    }
    
    
    /**
     *
     * @param rst
     * @return List<Article>
     * @throws SQLException
     */
    private List<Article> map(ResultSet rst) throws SQLException {
        List<Article> articles = new ArrayList<>();

        while (rst.next()) {
            Article article = new Article();
            article.setIdAppli(rst.getInt("idAppli"));
            article.setIdBase(rst.getInt("idBase"));
            article.setLibelle(rst.getString("libelle"));
            article.setReference(rst.getString("reference"));
            articles.add(article);
        }

        return articles;

    }
    /**
     *
     * @param rst
     * @return List<Prix>
     * @throws SQLException
     */
    private List<Prix> mapPrix(ResultSet rst) throws SQLException {
        List<Prix> listPrix = new ArrayList<>();

        while (rst.next()) {
            Prix prix = new Prix();
            prix.setDate(rst.getDate("date"));
            prix.setIdAppli(rst.getInt("idAppli"));
            prix.setIdBase(rst.getInt("idBase"));
            prix.setPrixUht(rst.getDouble("prixUht"));
            listPrix.add(prix);
        }

        return listPrix;
    }
//    /**
//     *
//     * @param rst
//     * @return List<DteilCommande>
//     * @throws SQLException
//     */
//    private List<DetailCommande> mapDetailCommande(ResultSet rst) throws SQLException {
//        List<DetailCommande> detailCommandes = new ArrayList<>();
//
//        while (rst.next()) {
//            DetailCommande detailCommande = new DetailCommande();
//            detailCommande.setIdAppli(rst.getInt("idAppli"));
//            detailCommande.setIdBase(rst.getInt("idBase"));
//            detailCommande.setQuantite(rst.getInt("quantite"));
//            detailCommandes.add(detailCommande);
//        }
//        return detailCommandes;
//    }
//    /**
//     *
//     * @param rst
//     * @return List<DetailDemande>
//     * @throws SQLException
//     */
//    private List<DetailDemande> mapDetailDemande(ResultSet rst) throws SQLException {
//        List<DetailDemande> detailDemandes = new ArrayList<>();
//
//        while (rst.next()) {
//            DetailDemande detailDemande = new DetailDemande();
//            detailDemande.setIdAppli(rst.getInt("idAppli"));
//            detailDemande.setIdBase(rst.getInt("idBase"));
//            detailDemande.setQuantite(rst.getInt("quantite"));
//            detailDemandes.add(detailDemande);
//        }
//        return detailDemandes;
//    }
    /**
     *
     * @param article
     * @return List<Prix>
     * @throws DAOException
     */
    @Override
    public List<Prix> getPrixParArticle(Article article) throws DAOException {
        if (article == null) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT p.idAppli AS idAppli, p.idBase AS idBase, prixUht, p.date ");
        builder.append("FROM article a INNER JOIN prix p ON p.idAppliArticle = a.idAppli ");
        builder.append("WHERE a.idAppli = ?");

        String request = builder.toString();
        logger.debug("get prix: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }
        try {
            ps.setInt(1, article.getIdAppli());
            ps.executeQuery();
        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }
        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }
        List<Prix> listPrix = null;
        try {
            listPrix = mapPrix(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.");
            throw new DAOException("Can not populate map with result.", e);
        }

//        if (null == listPrix || listPrix.isEmpty()) {
//            throw new NoResultException("No prix found with article : " + article);
//        }
        manager.fermerConnection();
        return listPrix;
    }
    /**
     *
     * @param article
     * @return List<Prix>
     * @throws DAOException
     */
    @Override
    public List<Prix> getPrixMajParArticle(Article article) throws DAOException {
        if (article == null) {
            throw new IllegalArgumentException("Can not save a null object.");
        }
        
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT p.idAppli AS idAppli, p.idBase AS idBase, prixUht, p.date ");
        builder.append("FROM article a INNER JOIN prix p ON p.idAppliArticle = a.idAppli "
                + "JOIN client c ON c.idAppli = a.idAppliClient "
                + "JOIN utilisateur u ON u.idAppli = c.idAppliUtilisateur ");
        builder.append("WHERE a.idAppli = ? "
                + "AND (p.idBase = 0 OR p.dateModification > u.dateSynchro)");



        String request = builder.toString();
        logger.debug("get prix: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }
        try {
            ps.setInt(1, article.getIdAppli());
            ps.executeQuery();
        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }
        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }
        List<Prix> listPrix = null;
        try {
            listPrix = mapPrix(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.");
            throw new DAOException("Can not populate map with result.", e);
        }

//        if (null == listPrix || listPrix.isEmpty()) {
//            throw new NoResultException("No prix found with article : " + article);
//        }
        manager.fermerConnection();
        return listPrix;
    }

    /**
     * 
     * @param prix
     * @param article
     * @throws DAOException 
     */
    @Override
    public void lierPrixAArticle(Prix prix, Article article) throws DAOException {
        if (null == article || null == prix) {
            throw new IllegalArgumentException("Can not save a null object.");
        }
      
        Connection con = manager.creerConnection();

        StringBuilder builder = new StringBuilder();
        builder.append("UPDATE prix SET ");
        builder.append("idAppliArticle = ?,");
        builder.append("dateModification = NOW() ");
        builder.append("WHERE idAppli = ?");

        String request = builder.toString();
        logger.debug("Update prix: {}", request);


        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, article.getIdAppli());
            ps.setInt(2, prix.getIdAppli());
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not populate request with data.");
            throw new DAOException("Can not populate request with data.", e);
        }
        manager.fermerConnection();
    }
//    /**
//     *
//     * @param article
//     * @return List<DetailCommande>
//     * @throws DAOException
//     */
////    @Override
//    public List<DetailCommande> getDetailCommandesParArticle(Article article) throws DAOException {
//        if (article == null) {
//            throw new IllegalArgumentException("Can not save a null object.");
//        }
//
//        StringBuilder builder = new StringBuilder();
//        builder.append("SELECT d.idAppli AS idAppli, d.idBase AS idBase, quantite ");
//        builder.append("FROM article a INNER JOIN detailcommande d ON d.idAppliArticle = a.idAppli ");
//        builder.append("WHERE a.idAppli = ?");
//
//        String request = builder.toString();
//        logger.debug("get detailcommande: {}", request);
//
//        Connection con = manager.creerConnection();
//        PreparedStatement ps;
//        try {
//            ps = con.prepareStatement(request);
//        } catch (SQLException e) {
//            logger.error("Can not prepare statement");
//            throw new DAOException("Can not prepare statement.", e);
//        }
//        try {
//            ps.setInt(1, article.getIdAppli());
//            ps.executeQuery();
//        } catch (SQLException e) {
//            logger.error("Can not select data with id.");
//            throw new DAOException("Can not select data with id.", e);
//        }
//        ResultSet rst;
//        try {
//            rst = ps.getResultSet();
//        } catch (SQLException e) {
//            logger.error("Can not get resultset.");
//            throw new DAOException("Can not get resultset.", e);
//        }
//        List<DetailCommande> listDetailCommande = null;
//        try {
//            listDetailCommande = mapDetailCommande(rst);
//        } catch (SQLException e) {
//            logger.error("Can not populate map with result.");
//            throw new DAOException("Can not populate map with result.", e);
//        }
//
//        if (null == listDetailCommande || listDetailCommande.isEmpty()) {
//            throw new NoResultException("No detailCommande found with article : " + article);
//        }
//        return listDetailCommande;
//    }
//
//    /**
//     * 
//     * @param detailCommande
//     * @param article
//     * @throws DAOException 
//     */
////    @Override
//    public void lierDetailCommandeAArticle(DetailCommande detailCommande, Article article) throws DAOException{
//        if (null == article || null == detailCommande) {
//            throw new IllegalArgumentException("Can not save a null object.");
//        }        
//
//        Connection con = manager.creerConnection();
//
//        StringBuilder builder = new StringBuilder();
//        builder.append("UPDATE detailcommande SET ");
//        builder.append("idAppliArticle = ?,");
//        builder.append("dateModification = NOW() ");
//        builder.append("WHERE idAppli = ?");
//
//        String request = builder.toString();
//        logger.debug("Update detailcommande: {}", request);
//
//        PreparedStatement ps;
//        try {
//            ps = con.prepareStatement(request);
//        } catch (SQLException e) {
//            logger.error("Can not prepare statement");
//            throw new DAOException("Can not prepare statement.", e);
//        }
//
//        try {
//            ps.setInt(1, article.getIdAppli());
//            ps.setInt(2, detailCommande.getIdAppli());
//            ps.executeUpdate();
//        } catch (SQLException e) {
//            logger.error("Can not populate request with data.");
//            throw new DAOException("Can not populate request with data.", e);
//        }
//    }
//
//    /**
//     * 
//     * @param article
//     * @return
//     * @throws DAOException 
//     */
////    @Override
//    public List<DetailDemande> getDetailDemandesParArticle(Article article) throws DAOException {
//        if (article == null) {
//            throw new IllegalArgumentException("Can not save a null object.");
//        }
//
//        StringBuilder builder = new StringBuilder();
//        builder.append("SELECT d.idAppli AS idAppli, d.idBase AS idBase, quantite ");
//        builder.append("FROM article a INNER JOIN detaildemande d ON d.idAppliArticle = a.idAppli ");
//        builder.append("WHERE a.idAppli = ?");
//
//        String request = builder.toString();
//        logger.debug("get detaildemande: {}", request);
//
//        Connection con = manager.creerConnection();
//        PreparedStatement ps;
//        try {
//            ps = con.prepareStatement(request);
//        } catch (SQLException e) {
//            logger.error("Can not prepare statement");
//            throw new DAOException("Can not prepare statement.", e);
//        }
//        try {
//            ps.setInt(1, article.getIdAppli());
//            ps.executeQuery();
//        } catch (SQLException e) {
//            logger.error("Can not select data with id.");
//            throw new DAOException("Can not select data with id.", e);
//        }
//        ResultSet rst;
//        try {
//            rst = ps.getResultSet();
//        } catch (SQLException e) {
//            logger.error("Can not get resultset.");
//            throw new DAOException("Can not get resultset.", e);
//        }
//        List<DetailDemande> listDetailDemande = null;
//        try {
//            listDetailDemande = mapDetailDemande(rst);
//        } catch (SQLException e) {
//            logger.error("Can not populate map with result.");
//            throw new DAOException("Can not populate map with result.", e);
//        }
//
//        if (null == listDetailDemande || listDetailDemande.isEmpty()) {
//            throw new NoResultException("No detailCommande found with article " + article);
//        }
//        return listDetailDemande;
//    }
//
//    /**
//     * 
//     * @param detailDemande
//     * @param article
//     * @throws DAOException 
//     */
////    @Override
//    public void lierDetailDemandeAArticle(DetailDemande detailDemande, Article article) throws DAOException{
//        if (null == article || null == detailDemande) {
//            throw new IllegalArgumentException("Can not save a null object.");
//        }
//      
//        Connection con = manager.creerConnection();
//
//        StringBuilder builder = new StringBuilder();
//        builder.append("UPDATE detaildemande SET ");
//        builder.append("idAppliArticle = ?,");
//        builder.append("dateModification = NOW() ");
//        builder.append("WHERE idAppli = ?");
//
//        String request = builder.toString();
//        logger.debug("Update detaildemande: {}", request);
//
//        PreparedStatement ps;
//        try {
//            ps = con.prepareStatement(request);
//        } catch (SQLException e) {
//            logger.error("Can not prepare statement");
//            throw new DAOException("Can not prepare statement.", e);
//        }
//
//        try {
//            ps.setInt(1, article.getIdAppli());
//            ps.setInt(2, detailDemande.getIdAppli());
//            ps.executeUpdate();
//        } catch (SQLException e) {
//            logger.error("Can not populate request with data.");
//            throw new DAOException("Can not populate request with data.", e);
//        }
//    }
}
